** Coding Pub Data
use "/Users/pubUK.dta"
encode businesstype, gen(businesstype2)
encode holdingcompany, gen(company)
encode status, gen(status1)
encode currentpremisestatus, gen(currentstatus)

drop if closeddate > "2016-06-23"

***Create an Identifier of Local authority Areas
gen status =.
replace status = 0 if currentpremisestatus == "Demolished"
replace status = 0 if currentpremisestatus == "Vacant"
replace status = 0 if currentpremisestatus == "Closed"
replace status = 0 if currentpremisestatus == "Live"
replace status =1 if currentpremisestatus == "Split"
replace status =1 if currentpremisestatus == "Merged"

gen type=.
replace type=0 if businesstype=="Independent"
replace type=1 if businesstype=="Multiple"

drop currentpremisestatus businesstype
tab holdingcompany
gen chain=.
replace chain=1 if holdingcompany=="Greene King Plc"
replace chain=1 if holdingcompany=="Greene King Brewing and Retailing Limited"
replace chain=1 if holdingcompany=="Mitchells & Butlers PLC"
replace chain=1 if holdingcompany==" Punch Taverns PLC"
replace chain=1 if holdingcompany=="Punch Taverns PLC"
replace chain=1 if holdingcompany=="Stonegate Pub Co Ltd"
replace chain=1 if holdingcompany=="The Bramwell Pub Company"
replace chain=1 if holdingcompany=="Frederic Robinson Limited"
replace chain=0 if holdingcompany=="Adnams PLC"
drop holdingcompany

drop if status==1

bysort year localauthorityarea : gen howmany=_N
sort localauthorityarea year
quietly by localauthorityarea year :  gen dup = cond(_N==1,0,_n)
drop if dup>1

drop status type chain
drop dup
drop name area town
reshape wide howmany, i(localauthorityarea) j(year)
replace howmany2003=0 if howmany2003==.
replace howmany2004=0 if howmany2004==.
replace howmany2005=0 if howmany2005==.
replace howmany2006=0 if howmany2006==.
replace howmany2007=0 if howmany2007==.
replace howmany2008=0 if howmany2008==.
replace howmany2009=0 if howmany2009==.
replace howmany2010=0 if howmany2010==.
replace howmany2011=0 if howmany2011==.
replace howmany2012=0 if howmany2012==.
replace howmany2013=0 if howmany2013==.
replace howmany2014=0 if howmany2014==.
replace howmany2015=0 if howmany2015==.
replace howmany2016=0 if howmany2016==.

save "finalpub.dta"

